package J23;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import J22.J22_BookDBVO;

public class J23_MemberDAO implements J23_MemberService {
	// JDBC 접속정보
		private static final String DRV = "oracle.jdbc.OracleDriver";
		private static final String URL = "jdbc:oracle:thin:@127.0.0.1/xe";
		private static final String USR = "scott";
		private static final String PWD = "TIGER";
	
		private static final String insertSQL =
				"INSERT INTO MEMBER(USERID,JUMIN,PWD,ZIP,ADDR,TEL,MP,PAY)"
						+ " VALUES(?,?,?,?,?,?,?,?) ";
		private static final String updateSQL =
				"UPDATE MEMBER SET PWD = ?, ZIP =?, ADDR =?,TEL =?, MP =?, PAY =? WHERE USERID =?";
		private static final String deleteSQL =
				"delete from member where userid = ? ";
		private static final String selectSQL =
				"select userid, jumin, mp from member order by userid ";
		private static final String selectOneSQL =
				"select * from member where userid = ? ";
		
		
	
	@Override
	public int insertMember(J23_Member m) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(insertSQL);
				ps.setString(1, m.getUserid());
				ps.setString(2, m.getJumin());
				ps.setString(3, m.getPwd());
				ps.setString(4, m.getZip());
				ps.setString(5, m.getAddr());
				ps.setString(6, m.getTel());
				ps.setString(7, m.getMp());
				ps.setString(8, m.getPay());
			
			cnt = ps.executeUpdate();
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if( ps != null)
				try{ps.close();}catch(Exception e){}
			if(conn != null)
				try{conn.close();}catch(Exception e){}
		}
		return cnt;
	}

	@Override
	public int updateMember(J23_Member m) {
		int cnt = 0;
		return cnt;
	}

	@Override
	public int deleteMember(String m) {
		int cnt = 0;
		return cnt;
	}

	@Override
	public ArrayList<J23_Member> readMember() {
		ArrayList<J23_Member> data = new ArrayList<>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();
			
			// select문 실행 후 결과 처리 - 컬렉션 사용
			while(rs.next()){
				J23_Member m = 
						new J23_Member(rs.getString(1), rs.getString(2), "", "", 
								"", "", rs.getString(3), "");
				// 결과집합 rs에 저장된 모든 데이터 중
				// 레코드 단위(행)로 하나씩 읽어와서 
				// J23_Member 클래스에 저장하고 
				// 그것을 ArrayList data변수에 추가함
				data.add(m);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(rs != null)
				try{rs.close();}catch(Exception e){}
			if(ps != null)
				try{ps.close();}catch(Exception e){}
			if(conn != null)
				try{conn.close();}catch(Exception e){}
		}
		
			
		return data;
	}

	@Override
	public J23_Member readOneMember(String m) {
		J23_Member data = null;
		return data;
	}

}
